package net.ifilm.db;

import net.ifilm.common.Constants;
import net.ifilm.common.SpiderException;
import net.ifilm.domain.Film;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-7-16
 * Time: 上午10:47
 * To change this template use File | Settings | File Templates.
 */
public class DBExecutor  {

    private DBTemplate dbTemplate = new DBTemplate();

    public List<Film> findAll() throws Exception {
        return (List<Film>) dbTemplate.query("SELECT filmid, filmname, imagepath, mimagepath from film", new Object[]{}, new ResultCallback() {
            @Override
            public Object callback(ResultSet rs) throws SQLException {
                List<Film> filmList = new ArrayList<Film>();
                Film film = null;
                while (rs.next()) {
                    film = new Film();
                    film.setFilmId(rs.getInt("filmid"));
                    film.setFilmName(rs.getString("filmname"));
                    film.setImagePath(rs.getString("imagepath"));
                    film.setmImagePath(rs.getString("mimagepath"));
                    filmList.add(film);
                }
                return filmList;
            }
        });
    }

    public Map<String, Object> findFilmNamelist() throws Exception {
        return (Map<String, Object>) dbTemplate.query(Constants.FILMNAME_LIST, new Object[]{}, new ResultCallback() {
            @Override
            public Object callback(ResultSet rs) throws SQLException {
                Map<String, Object> filmNameMap = new HashMap<String, Object>();
                while (rs.next()) {
                    filmNameMap.put(rs.getString("filmname"), rs.getInt("nums"));
                }
                return filmNameMap;
            }
        });
    }

    public Map<String, Object> findFilmActorList() throws Exception {
        return (Map<String, Object>) dbTemplate.query(Constants.FILMACTOR_LIST, new Object[]{}, new ResultCallback() {
            @Override
            public Object callback(ResultSet rs) throws SQLException {
                Map<String, Object> filmActorMap = new HashMap<String, Object>();
                while (rs.next()) {
                    filmActorMap.put(rs.getString("starname"), rs.getInt("id"));
                }
                return filmActorMap;
            }
        });
    }

    public Map<String, Object> findReverseFilmNameMap() throws Exception {
        return (Map<String, Object>) dbTemplate.query(Constants.REVERSE_FILMNAME_LIST, new Object[]{}, new ResultCallback() {
            @Override
            public Object callback(ResultSet rs) throws SQLException {
                Map<String, Object> filmNameMap = new HashMap<String, Object>();
                while (rs.next()) {
                    filmNameMap.put(rs.getString("filmname"), rs.getInt("filmid"));
                }
                return filmNameMap;
            }
        });
    }

    public Map<String, Object> findFilmPath(int filmId) throws Exception {
        return (Map<String, Object>) dbTemplate.query(Constants.FILMPATH_LIST, new Object[]{ filmId }, new ResultCallback() {
            @Override
            public Object callback(ResultSet rs) throws SQLException {
                Map<String, Object> filmPathMap = new HashMap<String, Object>();
                while (rs.next()) {
                    filmPathMap.put(rs.getString("labeltitle"), rs.getInt("ordernum"));
                }
                return filmPathMap;
            }
        });
    }

    public void updateFileTime(Date updateTime, int filmid) throws Exception {
        dbTemplate.update(Constants.UPDATE_FILM_TIME, new Object[]{ updateTime, filmid });
    }

    public void existBatchInsert(int filmId, Map<String, Object> filmPathMap, Map<String, String> playUrlMap) throws Exception {
        Connection conn = null;
        try {
            Statement stmt = null;
            try {
                conn = DBConnectionUtil.getInstance().getConnection();
                conn.setAutoCommit(false);
                stmt = conn.createStatement();

                StringBuffer sql = new StringBuffer();
                String labelTitle = null;
                Object orderNumValue = null;
                int orderNum = 0;
                for (Map.Entry<String, String> entry : playUrlMap.entrySet()) {
                    labelTitle = entry.getKey();
                    orderNumValue = filmPathMap.get(labelTitle);
                    if (orderNumValue != null) {
                        orderNum = (Integer)orderNumValue;
                        continue;
                    }
//                    orderNum = (Integer)orderNumValue;

                    sql.append("INSERT INTO filmpath (filmid, netpath, ordernum, playertype, labeltitle) values (")
                            .append(filmId).append(", '").append(entry.getValue()).append("', ")
                            .append(++orderNum).append(", 2, '").append(labelTitle).append("')");
                    stmt.addBatch(sql.toString());
                    sql.setLength(0);
                }

                stmt.executeBatch();
                conn.commit();
            } finally {
                stmt.close();
                conn.close();
            }
        } catch (SQLException e) {
            conn.rollback();

            throw new Exception(e);
        }
    }







    public int queryFilmByName(String filmName) throws SpiderException {
        String sql = "SELECT filmid FROM film WHERE filmname = ?";
        Object[] params = new Object[1];
        params[0] = filmName;

        return (Integer)dbTemplate.query(sql, params, new ResultCallback() {
            @Override
            public Object callback(ResultSet rs) throws SQLException {
                if (rs.next()) return rs.getInt("filmid");
                return 0;
            }
        });
    }

    public int insertFilm(Film film) throws SpiderException {
        int filmId = 0;

        String sql = "INSERT INTO film(regionid, filmname, filmintroduce, filmtime, imagepath, mimagepath, showtime, islimit," +
                " ishot, isclear, tags, createtime, playnum, type)" +
                " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, round(round(rand(),4)*1000), ?)";
        Object[] params = new Object[13];
        params[0] = film.getRegionId();
        params[1] = film.getFilmName();
        params[2] = film.getFilmIntroduce();
        params[3] = film.getFilmTime();
        params[4] = film.getImagePath();
        params[5] = film.getmImagePath();
        params[6] = film.getShowTime();
        params[7] = film.getLimit();
        params[8] = film.getHot();
        params[9] = film.getClear();
        params[10] = film.getTags();
        params[11] = film.getCreateTime();
        params[12] = film.getType();

        int updateCount = (Integer)dbTemplate.update(sql, params);
        if (updateCount != 0) {
            filmId = queryFilmByName(film.getFilmName());
        }
        return filmId;
    }

    public int insertRelFilmType(int filmId, int filmTypeId) throws SpiderException {
        String sql = "INSERT INTO relfilmtype (filmid, filmtypeid) VALUES (?, ?)";
        Object[] params = new Object[2];
        params[0] = filmId;
        params[1] = filmTypeId;

        int updateCount = (Integer)dbTemplate.update(sql, params);

        return updateCount;
    }

    public int insertRelStar(int filmId, int starId) throws SpiderException {
        String sql = "INSERT INTO relfilmstar (filmid, starid) VALUES (?, ?)";
        Object[] params = new Object[2];
        params[0] = filmId;
        params[1] = starId;
        return (Integer)dbTemplate.update(sql, params);
    }

    public int queryStarByName(String starName) throws SpiderException {
        String sql = "SELECT id FROM star WHERE starname = ?";
        Object[] params = new Object[1];
        params[0] = starName;
        return (Integer)dbTemplate.query(sql, params, new ResultCallback() {
            @Override
            public Object callback(ResultSet rs) throws SQLException {
                if (rs.next()) return rs.getInt("id");
                return 0;
            }
        });
    }

    public int insertStar(String starName) throws SpiderException {
        int starId = 0;
        String sql = "INSERT INTO star (starName) VALUES (?)";
        Object[] params = new Object[1];
        params[0] = starName;

        int updateCount =  (Integer)dbTemplate.update(sql, params);
        if (updateCount != 0) {
            starId = queryStarByName(starName);
        }
        return starId;
    }

    public void batchInsert(int filmId, Map<String, String> urlMap) {
        Connection conn = null;
        try {
            Statement stmt = null;
            try {
                conn = DBConnectionUtil.getInstance().getConnection();
                conn.setAutoCommit(false);
                stmt = conn.createStatement();
                String sql = null;
                int i = 0;
                for (Map.Entry<String, String> entry : urlMap.entrySet()) {
                    sql = "INSERT INTO filmpath (filmid, netpath, ordernum, playertype, labeltitle) values (" + filmId
                            + ", '" + entry.getValue() + "', " + (i++) + ", 2, '" + entry.getKey() + "')";
                    stmt.addBatch(sql);
                }
                stmt.executeBatch();
                conn.commit();
            } catch (SpiderException e) {
                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
            } finally {
                stmt.close();
                conn.close();
            }
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
}



}